package Dao;

import model.Manager;
import model.PageInfo;
import model.Student;
import util.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author cyt
 * @date 2020/8/2
 */
public class ManagerDaoImpl implements ManagerDao {
    @Override
    public Manager login(Manager manager) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection = DruidUtils.getConnection();

        try {
            String sql = "SELECT * FROM manager where username = ? and password = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, manager.getUsername());
            preparedStatement.setString(2, manager.getPassword());
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                return new Manager(resultSet.getString("username"),resultSet.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(connection,preparedStatement,resultSet);
        }

       return null;
    }

    @Override
    public int insert(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection = DruidUtils.getConnection();

        try {
            String sql = "INSERT INTO student values(null,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getSex());
            preparedStatement.setDate(3,  new java.sql.Date(student.getBirthday().getTime()));
            preparedStatement.setString(4, student.getEmail());
            preparedStatement.setString(5, student.getRemark());
            preparedStatement.setInt(6,student.getClass_id());

            int result = preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(connection,preparedStatement,resultSet);
        }
        return 0;

    }

    @Override
    public int update(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection = DruidUtils.getConnection();

        try {
            String sql = "UPDATE student SET name = ?,sex = ? ,birthday = ? , email = ?, remark = ?, class_id = ? WHERE student_num = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setString(2, student.getSex());
            preparedStatement.setDate(3,  new java.sql.Date(student.getBirthday().getTime()));
            preparedStatement.setString(4, student.getEmail());
            preparedStatement.setString(5, student.getRemark());
            preparedStatement.setInt(6,student.getClass_id());
            preparedStatement.setInt(7,student.getStudentNum());
            int result = preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(connection,preparedStatement,resultSet);
        }
        return 0;
    }

    @Override
    public int delete(String[] ids) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.获取Connection连接对象
            connection = DruidUtils.getConnection();
            // 2.准备插入数据的SQL语句
            // 2.1 拼接一下所有的id
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < ids.length; i++) {
                if (i == ids.length-1) {
                    sb.append(ids[i]);
                } else {
                    sb.append(ids[i]).append(",");
                }
            }
            // System.out.println("sb = " + sb);
            // 2.2 拼接sql语句
            String sql = "delete from student where student_num in(" + sb + ")";
            // 3.获取PreparedStatement并执行SQL语句
            preparedStatement = connection.prepareStatement(sql);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(connection, preparedStatement);
        }
        return 0; // 表示删除失败
    }

    @Override
    public Student findByNum(Integer studentNum) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection = DruidUtils.getConnection();

        try {
            String sql = "SELECT * FROM student WHERE student_num = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,studentNum);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                return new Student(resultSet.getInt("student_num"),resultSet.getString("name"),
                        resultSet.getString("sex"),resultSet.getDate("birthday"),resultSet.getString("email"),
                        resultSet.getString("remark"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(connection,preparedStatement,resultSet);
        }
        return null;
    }

    @Override
    public List<Student> findAll(PageInfo pageInfo) {
        List<Student> studentList = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取Connection连接对象
            connection = DruidUtils.getConnection();
            // 2.使用StringBuilder类型的对象来描述SQL语句的目的在于后续可以发生改变
            StringBuilder stringBuilder = new StringBuilder("select * from student");
            // 3.当输入分页的需求时进行分页查询，于是拼接SQL语句
            if (pageInfo != null) {
                stringBuilder.append(" limit " + pageInfo.getStart() + "," + pageInfo.getRow());
            }
            // 4.获取PreparedStatement并执行上述SQL语句
            preparedStatement = connection.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            // 5.将结果集中的数据取出并放到List集合中返回
            studentList = new ArrayList<>();
            while (resultSet.next()) {
                Student student = new Student(resultSet.getInt("class_id"),resultSet.getInt("student_num"), resultSet.getString("name"),
                        resultSet.getString("sex"),(resultSet.getDate("birthday")),
                        resultSet.getString("email"), resultSet.getString("remark"));
                studentList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(connection, preparedStatement, resultSet);
        }
        return studentList;
    }

    @Override
    public int size() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        connection = DruidUtils.getConnection();

        try {
            String sql = "SELECT count(*) AS size FROM student";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                return resultSet.getInt("size");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.close(connection,preparedStatement,resultSet);
        }
        return 0;
    }

}
